package com.ningxia.water.mapper;

import com.ningxia.water.entity.ZtVillageInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.ReportVo;
import com.ningxia.water.vo.out.ZonePidVo;
import com.ningxia.water.vo.out.ZoneVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author hz
 * @since 2021-05-14
 */
public interface ZtVillageInfoMapper extends BaseMapper<ZtVillageInfo> {

    @Select("SELECT\n" +
            "\tt.town_id as pid,\n" +
            "\tv.village_id as zoneId,\n" +
            "\tc.city_name AS city,\n" +
            "\td.district_name AS district,\n" +
            "\tt.town_name AS town,\n" +
            "\tvillage_name AS village,\n" +
            "\tv.address as address\n" +
            "FROM\n" +
            "\tzt_district_info d,\n" +
            "\tzt_city_info c,\n" +
            "\tzt_town_info t,\n" +
            "\tzt_village_info v \n" +
            "WHERE\n" +
            "\td.city_id = c.city_id \n" +
            "\tAND t.district_id = d.district_id \n" +
            "\tAND t.town_id = v.town_id")
    List<ZoneVo> getVillageInfos();

    /**
     * 获取指定村庄id的机井地址
     * @param zoneId
     * @return
     */
    @Select("select address from zt_village_info where village_id = #{zoneId}")
    String getWellAddress(Integer zoneId);

    /**
     * 在村庄模糊查询地址
     * @param address
     * @return
     */
    @Select("select address from zt_village_info " +
            "where village_id = #{zoneId} and  address like CONCAT('%',#{address},'%')")
    String findWellAddressByLike(String address,Integer zoneId);

    /**
     * 获取村庄年报数据
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "vi.village_id as id, " +
            "vi.village_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_village_info vi\n" +
            "LEFT JOIN ts_well_info wi ON wi.zone_id = vi.village_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time BETWEEN concat(#{currentTime}, '-01') and concat(#{currentTime}, '-12') \n" +
            "GROUP BY vi.village_id")
    List<ReportVo> getVillageReportByYear(String currentTime);

    /**
     * 获取村庄月报数据
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "vi.village_id as id, " +
            "vi.village_name as name, " +
            "SUM(md.month_data)/10000 as volume\n" +
            "FROM zt_village_info vi\n" +
            "LEFT JOIN ts_well_info wi ON wi.zone_id = vi.village_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time = #{currentTime}\n" +
            "GROUP BY vi.village_id")
    List<ReportVo> getVillageReportByMonth(String currentTime);

    /**
     * 获取村庄日报数据
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "vi.village_id as id, " +
            "vi.village_name as name, " +
            "sum(dd.day_data)/10000 as volume\n" +
            "FROM zt_village_info vi\n" +
            "LEFT JOIN ts_well_info wi ON wi.zone_id = vi.village_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_day_data dd ON wi.well_id = dd.well_id and dd.time = #{currentTime}\n" +
            "GROUP BY vi.village_id")
    List<ReportVo> getVillageReportByDay(String currentTime);

    /**
     * 获取村庄对应乡镇id
     * @return
     */
    @Select("SELECT\n" +
            "\tti.town_id AS pid,\n" +
            "\tvi.village_id AS id,\n" +
            "\tvi.village_name AS name \n" +
            "FROM\n" +
            "\tzt_village_info vi\n" +
            "\tLEFT JOIN zt_town_info ti ON vi.town_id = ti.town_id")
    List<ZonePidVo> getVillageRelationship();
}
